package com.ctshk.rpc.bus.mapper;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ctshk.common.enums.ApprovalStatus;
import com.ctshk.common.service.mapper.CtsBaseMapper;
import com.ctshk.common.service.mapper.CtsQueryWrapper;
import com.ctshk.common.service.mapper.CtsSQL;
import com.ctshk.common.utils.Assist;
import com.ctshk.rpc.bus.dto.BusB2CRecommendedDTO;
import com.ctshk.rpc.bus.dto.BusProductDTO;
import com.ctshk.rpc.bus.dto.RecommendedBusDTO;
import com.ctshk.rpc.bus.dto.ReservationBusPageDTO;
import com.ctshk.rpc.bus.entity.BusProduct;
import com.ctshk.rpc.bus.req.BusB2CRecommendedListQueryReq;
import com.ctshk.rpc.bus.req.BusProductListForIotcQueryReq;
import com.ctshk.rpc.bus.req.BusProductQueryReq;
import com.ctshk.rpc.bus.req.ReservationBusPageReq;

/**
 * <p>
 * 巴士产品表 Mapper 接口
 * </p>
 *
 * @author 彭柳霖
 * @since 2021-02-08
 */
public interface BusProductMapper extends CtsBaseMapper<BusProduct> {

    /**
     * 查询巴士票推荐列表(至多15个)
     * @return
     */
    @Select("<script>"+"select b.id busProductId,b.bus_companies_name,b.departure_city_name," +
            "b.arrive_city_name,b.boarding_station_name,b.alighting_station_name," +
            "b.supplier_id,b.product_source,b.price_type,b.price productPrice," +
            "d.price \n" +
            "from t_bus_product_cost a \n" +
            "left join t_bus_product b on a.bus_product_id = b.id \n" +
            "left join t_bus_product_cost_date c on a.id  = c.cost_id \n" +
            "left join t_bus_product_cost_price d on a.id =d.cost_id \n" +
            "WHERE b.is_deleted = 0 and b.sale_status = 1 \n" +
            "AND b.recommendation_status = 1 \n" +
            "AND c.date_begin &lt;=now() and c.date_end &gt;=now()\n" +
            "AND d.passenger_id = 1 \n" +
            "group by b.id order by b.gmt_create desc limit 15 \n" +
            "</script>")
    List<RecommendedBusDTO> queryRecommendedBusTicket();

    /**
     * C端查询巴士预订推荐列表
     */
    @SelectProvider(type = BusProductMapper.class, method = "queryB2CRecommendedListProvider")
    List<BusB2CRecommendedDTO> queryB2CRecommendedList(BusB2CRecommendedListQueryReq req);
    
    static String queryB2CRecommendedListProvider(BusB2CRecommendedListQueryReq req) {
    	return new CtsSQL() {{
    		SELECT("distinct b.id busProductId,b.bus_companies_name,b.departure_city_name,b.arrive_city_name,b.boarding_station_name"
    				, "b.alighting_station_name, b.price_type, b.price_type,b.price productPrice, d.price, b.product_source");
    		FROM("t_bus_product_cost a");
    		LEFT_OUTER_JOIN("t_bus_product b on a.bus_product_id = b.id"
    				, "t_bus_product_cost_date c on a.id  = c.cost_id"
    				, "t_bus_product_cost_price d on a.id =d.cost_id");
    		WHERE("b.is_deleted = 0 and b.sale_status = 1 AND b.recommendation_status = 1 AND c.date_begin <= now() and c.date_end >= now()"
    				, "d.passenger_id = 1 and b.departure_city_id = #{departureCityId}");
    		ORDER_BY("b.gmt_create desc");
    		LIMIT(15);
    	}}.toString();
    }


//    /**
//     * 预订中心-查询巴士票列表
//     * @param req
//     * @return
//     */
//    @Select("<script>"+"select b.id busProductId,b.product_source,b.departure_time,b.shift_code,b.boarding_station_name,c.gateway_name," +
//            "b.alighting_station_name,c.vehicle_type," +
//            "b.price_type,b.price productPrice," +
//            "c.estimated_time,b.intended_for,b.supplier_name,d.id costId, b.inventory \n" +
//            "from t_bus_product b \n" +
//            "left join t_bus_line c on b.line_id =c.id \n" +
//            "left join t_bus_product_cost d on b.id = d.bus_product_id \n" +
//            "left join t_bus_product_cost_date e on d.id = e.cost_id \n" +
//            "WHERE b.is_deleted = 0 and b.sale_status = 1 \n" +
//            "AND e.date_begin &lt;=#{req.queryDate} and e.date_end &gt;=#{req.queryDate}\n" +
//            "<if test=\"req.busCompaniesName != null and req.busCompaniesName != ''\">AND b.bus_companies_name like concat('%', #{req.busCompaniesName}, '%')</if> \n" +
//            "<if test=\"req.departureCityName != null and req.departureCityName != ''\">AND b.departure_city_name like concat('%', #{req.departureCityName}, '%')</if> \n" +
//            "<if test=\"req.arriveCityName != null and req.arriveCityName != ''\">AND b.arrive_city_name like concat('%', #{req.arriveCityName}, '%')</if> \n" +
//            "<if test=\"req.boardingStationName != null and req.boardingStationName != ''\">AND b.boarding_station_name like concat('%', #{req.boardingStationName}, '%')</if> \n" +
//            "<if test=\"req.alightingStationName != null and req.alightingStationName != ''\">AND b.alighting_station_name like concat('%', #{req.alightingStationName}, '%')</if> \n" +
//            "group by b.id order by b.departure_time asc \n" +
//            "</script>")
//    IPage<ReservationBusPageDTO> queryReservationBusList2(@Param("req") ReservationBusPageReq req, IPage<ReservationBusPageDTO> agentPage);
    
    /**
     * 预订中心-查询巴士票列表
     * @param req
     * @return
     */
    @SelectProvider(type = BusProductMapper.class, method = "queryReservationBusListProvider")
    IPage<ReservationBusPageDTO> queryReservationBusList(@Param("req") ReservationBusPageReq req, IPage<ReservationBusPageDTO> agentPage);
    
    static String queryReservationBusListProvider(ReservationBusPageReq req) {
    	return new CtsSQL() {{
    		SELECT_DISTINCT("b.id busProductId,b.product_source,b.departure_time,b.shift_code,b.boarding_station_name,c.gateway_name"
    				, "b.alighting_station_name,c.vehicle_type,b.price_type,b.price productPrice"
    				, "c.estimated_time,b.intended_for,b.intended_for_name,b.supplier_name,d.id costId, b.inventory, tprs.is_need_certificates"
    				, "d.currency_id");
    		FROM("t_bus_product b");
    		JOIN("t_bus_product_cost d on b.id = d.bus_product_id"
    				, "t_bus_product_cost_date e on d.id = e.cost_id");
    		LEFT_OUTER_JOIN("t_bus_line c on b.line_id =c.id");
    		LEFT_OUTER_JOIN("t_bus_product_reservation_settings tprs on tprs.bus_product_id = b.id");
    		WHERE("b.is_deleted = 0 and b.sale_status = 1 AND e.date_begin <= #{req.queryDate} and e.date_end >= #{req.queryDate} and d.currency_id is not null"
    				, "b.on_shelf_status = 1");
    		
    		if (Assist.gtZero(req.getPassengerAdultNum()))
    			WHERE("exists (select 1 from t_bus_product_cost_price tcp where tcp.cost_id = d.id and tcp.passenger_id = 1 limit 1)");
    		if (Assist.gtZero(req.getPassengerChildrenNum()))
    			WHERE("exists (select 1 from t_bus_product_cost_price tcp where tcp.cost_id = d.id and tcp.passenger_id = 2 limit 1)");
    		if (Assist.gtZero(req.getPassengerOldNum()))
    			WHERE("exists (select 1 from t_bus_product_cost_price tcp where tcp.cost_id = d.id and tcp.passenger_id = 3 limit 1)");
    		
    		IF_NOT_BLANK_LIKE(req.getBusCompaniesName(), "b.bus_companies_name", "req.busCompaniesName");
    		IF_NOT_BLANK_LIKE(req.getDepartureCityName(), "b.departure_city_name", "req.departureCityName");
    		IF_NOT_BLANK_LIKE(req.getArriveCityName(), "b.arrive_city_name", "req.arriveCityName");
    		IF_NOT_BLANK_LIKE(req.getBoardingStationName(), "b.boarding_station_name", "req.boardingStationName");
    		IF_NOT_BLANK_LIKE(req.getAlightingStationName(), "b.alighting_station_name", "req.alightingStationName");
    		
    		ORDER_BY("b.departure_time asc");
    	}}.toString();
    }

    @Select("<script>"+"select b.id busProductId,b.product_source,b.departure_time,b.shift_code,b.boarding_station_name,c.gateway_name," +
            "b.alighting_station_name,c.vehicle_type," +
            "b.price_type,b.price productPrice," +
            "c.estimated_time,b.intended_for,b.supplier_name,d.id costId, "
            + "b.departure_city_id, b.departure_city_name, b.arrive_city_id, b.arrive_city_name, b.inventory, d.currency_id \n" +
            "from t_bus_product b \n" +
            "join t_bus_line c on b.line_id =c.id \n" +
            "join t_bus_product_cost d on b.id = d.bus_product_id \n" +
            "join t_bus_product_cost_date e on d.id = e.cost_id \n" +
            "WHERE b.is_deleted = 0 and b.id = #{id} and d.currency_id is not null limit 1" + 
            "</script>")
    ReservationBusPageDTO queryReservationBusDetail(Long id);
    
    /**
     * 运营中心查询巴士产品列表
     */
    default IPage<BusProductDTO> queryProductListForIotc(BusProductListForIotcQueryReq req) {
    	CtsQueryWrapper<BusProduct> queryWrapper = newCtsWrapper()
    			.ifNotBlankLike("product_code", req.getProductCode())
    			.ifNotNullEq("departure_city_id", req.getDepartureCityId())
    			.ifNotNullEq("arrive_city_id", req.getArriveCityId())
    			.ifNotBlankLike("shift_code", req.getShiftCode())
    			.ifNotNullEq("supplier_id", req.getSupplierId())
    			.ifNotNullEq("bus_companies_id", req.getBusCompaniesId())
    			.ifNotBlankLike("bus_companies_name", req.getBusCompaniesName())
    			.ifNotBlankLike("departure_city_name", req.getDepartureCityName())
    			.ifNotBlankLike("arrive_city_name", req.getArriveCityName())
    			.ifNotBlankLike("boarding_station_name", req.getBoardingStationName())
    			.ifNotBlankLike("alighting_station_name", req.getAlightingStationName());
    	return selectPage(BusProductDTO.class, req, queryWrapper);
    }
    
    /**
     * 查询巴士产品列表
     * @param req
     * @return
     */
    default IPage<BusProduct> queryProductList(BusProductQueryReq req, List<Long> permissionUserList) {
    	return queryProductListHandle(req, false, permissionUserList);
    }
    
    /**
     * 查询巴士产品列表，可售卖
     * @param req
     * @return
     */
    default IPage<BusProduct> queryApproveProductList(BusProductQueryReq req, List<Long> permissionUserList) {
    	return queryProductListHandle(req, true, permissionUserList);
    }
    
    /**
     * 查询巴士产品列表逻辑处理
     * @param req
     * @param isSale 是否售卖
     * @return
     */
    default IPage<BusProduct> queryProductListHandle(BusProductQueryReq req, boolean isSale, List<Long> permissionUserList) {
    	if (Assist.isEmpty(permissionUserList)) {
    		return new Page<>();
    	}
    	CtsQueryWrapper<BusProduct> queryWrapper = newCtsWrapper()
    			.ifNotNullEq("bus_companies_id", req.getBusCompaniesId())
    			.ifNotBlankLike("bus_companies_name", req.getBusCompaniesName())
    			.ifNotBlankLike("shift_code", req.getShiftCode())
    			.ifNotNullEq("departure_city_id", req.getDepartureCityId())
    			.ifNotBlankLike("departure_city_name", req.getDepartureCityName())
    			.ifNotNullEq("arrive_city_id", req.getArriveCityId())
    			.ifNotBlankLike("arrive_city_name", req.getArriveCityName())
    			.ifNotNullEq("boarding_station_id", req.getBoardingStationId())
    			.ifNotBlankLike("boarding_station_name", req.getBoardingStationName())
    			.ifNotNullEq("alighting_station_id", req.getAlightingStationId())
    			.ifNotBlankLike("alighting_station_name", req.getAlightingStationName())
    			.ifNotNullZeroEq("product_source", req.getProductSource())
    			.ifNotNullZeroEq("sale_status", req.getSaleStatus())
    			.ifNotNullEq("pack_status", req.getPackStatus())
    			.ifNotNullZeroEq("approval_status", req.getApprovalStatus())
    			.ifNotNullEq("supplier_id", req.getSupplierId())
    			.ifNotBlankLike("supplier_name", req.getSupplierName())
    			.ifNotNullZeroEq("recommendation_status", req.getRecommendationStatus())
    			.ifNotEmptyIn("charge_person_id", permissionUserList)
    			;
    	
    	//只查询审批通过的数据
    	if (isSale) {
    		queryWrapper.eq("sale_status", 1);
    	}
    	
    	queryWrapper.orderByDesc("gmt_create");
    	
    	return selectPage(req, queryWrapper);
    }
}
